package me.baicai.weixin.identity.repository;

    import com.alibaba.fastjson.JSONObject;
    import me.baicai.weixin.application.Result;
    import me.baicai.weixin.entity.identity.Agent;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.stereotype.Repository;

    import javax.transaction.Transactional;
    import java.util.List;


@Repository
public interface AgentRepository extends JpaRepository<Agent, Long>
{

    @Query(value = "select count(1) from device where shopId in (select p.id from shop p where p.agentId = ?)", nativeQuery = true)
    Integer getDeviceCount(String openid);

    @Query(value = "select count(1) from shop p where p.agentId = ?", nativeQuery = true)
    Integer getShopCount(String openid);

    @Query(value = "select ifnull(SUM(amount),0) from money_log where targetId = ?1 and to_days(created) = to_days(now())", nativeQuery = true)
    Float getToDayAmount(String openid);

    @Query(value = "select a.*,b.count from (select a.*,count(s.id) as shopCount  from agent a LEFT JOIN  shop s  on s.agentId = a.openid where a.parent = ?1 GROUP BY a.id) as a , (select a.*,count(d.id) as count from agent a LEFT JOIN  shop s  on s.agentId = a.openid LEFT JOIN device d on d.shopId = s.id where a.parent = ?1 GROUP BY a.id) as b where a.id = b.id", nativeQuery = true)
    List<JSONObject> findAllByParent(String openid, Pageable pageable);

    @Query(value = "select a.*,b.count from (select a.*,count(s.id) as shopCount  from agent a LEFT JOIN  shop s  on s.agentId = a.openid where a.parent = ?1 and a.id like ?2 or a.parent  = ?1 and `name` like ?2 GROUP BY a.id) as a , (select a.*,count(d.id) as count from agent a LEFT JOIN  shop s  on s.agentId = a.openid LEFT JOIN device d on d.shopId = s.id where a.parent = ?1 GROUP BY a.id) as b where a.id = b.id", nativeQuery = true)
    List<JSONObject> findLikeAll(String openid, String input, Pageable pageable);

    @Transactional
    @Modifying
    @Query("update Agent set name = ?1 ,area = ?2, telphone = ?3, commission = ?4 where id = ?5")
    int submitAgentInfo(String name, String address, String tel, Integer commission, Integer id);

    Agent findByOpenid(String openid);

    boolean existsByOpenid(String openid);

    @Query(value = "select d.sn,d.created,d.remark,d.`description`,ifnull(SUM(o.amount),0) as amount,s.shopName from device d LEFT JOIN `order` o on d.sn = o.deviceSN  LEFT JOIN shop s on d.shopId = s.id where s.agentId = ?1 GROUP BY d.id  order by amount desc ", nativeQuery = true)
    List<JSONObject> agentDeviceInfo(String openid, Pageable pageable);

    @Query(value = "select d.sn,d.created,d.remark,d.`description`,ifnull(SUM(o.amount),0) as amount,s.shopName from device d LEFT JOIN `order` o on d.sn = o.deviceSN  LEFT JOIN shop s on d.shopId = s.id where s.agentId = ?1  and d.sn like ?2 GROUP BY d.id", nativeQuery = true)
    List<JSONObject> agentDemviceInfo(String openid, String input, Pageable pageable);


    @Query(value = "select * from (select a.openid,a.`name`,i.id,count(d.id) as deviceCount,ifnull(SUM(o.amount),0) as orderAmount,ifnull(round((count(o.id)/count(d.id)),2),0) as rate ,ifnull(SUM(m.amount),0) as commAmount from agent a LEFT JOIN identity i on i.openid = a.openid LEFT JOIN shop s on s.agentId = a.openid LEFT JOIN device d on d.shopId = s.id LEFT JOIN `order` o on o.shopId = s.id and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' where a.parent = ?1 GROUP BY a.id,i.id) as a", nativeQuery = true)
    List<JSONObject> usageAnalysisByAgent(String openid, String date, Pageable pageable);

    @Query(value = "select * from (select a.openid,a.`name`,i.id,count(d.id) as deviceCount,ifnull(SUM(o.amount),0) as orderAmount,ifnull(round((count(o.id)/count(d.id)) / 30,2),0) as rate ,ifnull(SUM(m.amount),0) as commAmount from agent a LEFT JOIN identity i on i.openid = a.openid LEFT JOIN shop s on s.agentId = a.openid LEFT JOIN device d on d.shopId = s.id LEFT JOIN `order` o on o.shopId = s.id and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' where a.parent = ?1 GROUP BY a.id,i.id ) as a", nativeQuery = true)
    List<JSONObject> usageAnalysisByAgentByMonth(String openid, String date, Pageable pageable);

    @Query(value = "select * from (select a.id,a.shopName,b.deviceCount,a.orderCount,a.orderAmount,ifnull(round((a.orderCount / b.deviceCount) * 100,2),0) as rate,c.commAmount from (select s.id,s.shopName,count(o.orderNo) as orderCount,ifnull(sum(o.amount),0) as orderAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?2 and o.orderStatus = 2 where s.agentId = ?1 GROUP BY s.id ) as a LEFT JOIN (select s.id,s.shopName,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id  where s.agentId = ?1 GROUP BY s.id) as b on b.id = a.id LEFT JOIN (select s.id,s.shopName,ifnull(sum(m.amount),0) as commAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?2 and o.orderStatus = 2 LEFT JOIN money_log m on m.orderId = o.id and m.targetType = 'agent' and m.targetId = s.agentId where s.agentId = ?1 GROUP BY s.id) as  c on c.id = a.id ) as s", nativeQuery = true)
    List<JSONObject> usageAnalysisByShop(String openid, String date, Pageable pageable);

    @Query(value = "select * from (select a.id,a.shopName,b.deviceCount,a.orderCount,a.orderAmount,ifnull(round((a.orderCount / b.deviceCount) / 30 * 100,2),0) as rate,c.commAmount from (select s.id,s.shopName,count(o.orderNo) as orderCount,ifnull(sum(o.amount),0) as orderAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 and o.orderStatus = 2 where s.agentId = ?1 GROUP BY s.id ) as a LEFT JOIN (select s.id,s.shopName,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id  where s.agentId = ?1 GROUP BY s.id) as b on b.id = a.id LEFT JOIN (select s.id,s.shopName,ifnull(sum(m.amount),0) as commAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 and o.orderStatus = 2 LEFT JOIN money_log m on m.orderId = o.id and m.targetType = 'agent' and m.targetId = s.agentId where s.agentId = ?1 GROUP BY s.id) as  c on c.id = a.id ) as s", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopByMonth(String openid, String date, Pageable pageable);

    @Query(value = "select * from (select d.id,d.sn,s.shopName,d.remark,ifnull(SUM(o.amount),0) as orderAmount,count(o.id) as orderCount, ifnull(SUM(m.amount),0) as commAmount,count(r.id) as count from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2  LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' LEFT JOIN `order` r on r.deviceSN = d.sn  where s.agentId = ?1 GROUP BY d.id,s.id ) as d", nativeQuery = true)
    List<JSONObject> usageAnalysisByDevice(String openid, String date, Pageable pageable);

    @Query(value = "select * from (select d.id,d.sn,s.shopName,d.remark,ifnull(SUM(o.amount),0) as orderAmount,count(o.id) as orderCount, ifnull(SUM(m.amount),0) as commAmount,count(r.id) as count from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' LEFT JOIN `order` r on r.deviceSN = d.sn  where s.agentId = ?1  GROUP BY d.id,s.id ) as d", nativeQuery = true)
    List<JSONObject> usageAnalysisByDeviceByMonth(String openid, String date, Pageable pageable);

    @Query(value = "select a.id,a.shopName,a.deviceCount,b.orderCount,b.orderAmount,(b.orderCount / a.deviceCount) as rate, round((b.orderCount / a.deviceCount) * 100 , 2) as formatRate,c.commAmount from (select s.id,s.shopName,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.id = ?2 GROUP BY s.id) as a,(select s.id,s.shopName,count(o.orderNo) as orderCount,ifnull(sum(o.amount),0) as orderAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?3 and o.orderStatus = 2 where s.id = ?2 GROUP BY s.id) as b,(select ifnull(sum(m.amount),0) as commAmount from `order` o LEFT JOIN money_log m on m.orderId = o.id and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?3 where o.shopId = ?2 and m.targetType = 'agent' and m.targetId = ?1) as c", nativeQuery = true)
    JSONObject usageAnalysisByShopInfoInfo(String openid, Integer id, String date);

    @Query(value = "SELECT DATE_FORMAT(o.`created`, '%H') AS time ,DATE_FORMAT(o.`created`, '%H:00') AS date , DATE_FORMAT(o.`created`, '%Y-%m-%d %H:00') AS mon , COUNT(*) AS num FROM `order` o where o.shopId = ?1 and DATE_FORMAT(o.`created`, '%Y-%m-%d') = ?2 GROUP BY o.`created` ORDER BY o.`created`", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoOrderCount(Integer id, String date);

    @Query(value = "select d.sn,d.remark ,count(o.id) as count from device d LEFT JOIN `order` o on o.deviceSN = d.sn and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?2 where d.shopId = ?1 GROUP BY d.id ORDER BY count desc", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoLeast(Integer id, String date);

    @Query(value =
        "select a.*,ifnull((select count(o.id) from `order` o where o.shopId = ?1 and date(o.`end`) = a.time and o.orderStatus = 2)/(select count(d.id) from device d where shopId = ?1), 0) as rate " +
        "from (" +
        "    SELECT ?2 as time" +
        "    union all" +
        "    SELECT date_sub(?2, interval 1 day) as time" +
        "    union all" +
        "    SELECT date_sub(?2, interval 2 day) as time" +
        "    union all" +
        "    SELECT date_sub(?2, interval 3 day) as time" +
        "    union all" +
        "    SELECT date_sub(?2, interval 4 day) as time" +
        "    union all" +
        "    SELECT date_sub(?2, interval 5 day) as time" +
        "    union all" +
        "    SELECT date_sub(?2, interval 6 day) as time" +
        ") as a ORDER BY a.time", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoWeekly(Integer id, String date);

    @Query(value = "SELECT s.*,ifnull(round(s.count/count(d.id),1),0) as rate from (select s.id,s.shopName,count(o.id) as count from shop s LEFT JOIN `order` o on o.shopId = s.id where s.agentId = ?1 GROUP BY s.id) as s LEFT JOIN device d on d.shopId = s.id  GROUP BY s.id ORDER BY rate desc  LIMIT 7", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoRate(String openid);

    @Query(value = "select * from (select a.openid,a.`name`,i.id,count(d.id) as deviceCount,ifnull(SUM(o.amount),0) as orderAmount,ifnull(round((count(o.id)/count(d.id)),2),0) as rate ,ifnull(SUM(m.amount),0) as commAmount from agent a LEFT JOIN identity i on i.openid = a.openid LEFT JOIN shop s on s.agentId = a.openid LEFT JOIN device d on d.shopId = s.id LEFT JOIN `order` o on o.shopId = s.id and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' where a.parent = ?1 and a.name like ?3  GROUP BY a.id,i.id) as a", nativeQuery = true)
    List<JSONObject> usageAnalysisByAgent(String openid, String date, String input,
                                          Pageable pageable);

    @Query(value = "select * from (select a.openid,a.`name`,i.id,count(d.id) as deviceCount,ifnull(SUM(o.amount),0) as orderAmount,ifnull(round((count(o.id)/count(d.id)) / 30,2),0) as rate ,ifnull(SUM(m.amount),0) as commAmount from agent a LEFT JOIN identity i on i.openid = a.openid LEFT JOIN shop s on s.agentId = a.openid LEFT JOIN device d on d.shopId = s.id LEFT JOIN `order` o on o.shopId = s.id and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' where a.parent = ?1 and a.name like ?3  GROUP BY a.id,i.id ) as a", nativeQuery = true)
    List<JSONObject> usageAnalysisByAgentByMonth(String openid, String date, String s,
                                                 Pageable pageable);

    @Query(value = "select * from (select a.id,a.shopName,b.deviceCount,a.orderCount,a.orderAmount,ifnull(round((a.orderCount / b.deviceCount) * 100,2),0) as rate,c.commAmount from (select s.id,s.shopName,count(o.orderNo) as orderCount,ifnull(sum(o.amount),0) as orderAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?2 and o.orderStatus = 2 where s.agentId = ?1 GROUP BY s.id ) as a LEFT JOIN (select s.id,s.shopName,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id  where s.agentId = ?1 GROUP BY s.id) as b on b.id = a.id LEFT JOIN (select s.id,s.shopName,ifnull(sum(m.amount),0) as commAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m-%d') = ?2 and o.orderStatus = 2 LEFT JOIN money_log m on m.orderId = o.id and m.targetType = 'agent' and m.targetId = s.agentId where s.agentId = ?1 GROUP BY s.id) as  c on c.id = a.id ) as s where s.shopName like ?3 or s.id like ?3", nativeQuery = true)
    List<JSONObject> usageAnalysisByShop(String openid, String date, String s, Pageable pageable);

    @Query(value = "select * from (select a.id,a.shopName,b.deviceCount,a.orderCount,a.orderAmount,ifnull(round((a.orderCount / b.deviceCount) /30 * 100,2),0) as rate,c.commAmount from (select s.id,s.shopName,count(o.orderNo) as orderCount,ifnull(sum(o.amount),0) as orderAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 and o.orderStatus = 2 where s.agentId = ?1 GROUP BY s.id ) as a LEFT JOIN (select s.id,s.shopName,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id  where s.agentId = ?1 GROUP BY s.id) as b on b.id = a.id LEFT JOIN (select s.id,s.shopName,ifnull(sum(m.amount),0) as commAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 and o.orderStatus = 2 LEFT JOIN money_log m on m.orderId = o.id and m.targetType = 'agent' and m.targetId = s.agentId where s.agentId = ?1 GROUP BY s.id) as  c on c.id = a.id ) as s where s.shopName like ?3 or s.id like ?3", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopByMonth(String openid, String date, String s,
                                                Pageable pageable);

    @Query(value = "select * from (select d.id,d.sn,s.shopName,d.`description`,d.remark,ifnull(SUM(o.amount),0) as orderAmount,count(o.id) as orderCount, ifnull(SUM(m.amount),0) as commAmount,count(r.id) as count from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2  LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' LEFT JOIN `order` r on r.deviceSN = d.sn  where s.agentId = ?1 and d.sn like ?3 GROUP BY d.id,s.id ) as d", nativeQuery = true)
    List<JSONObject> usageAnalysisByDevice(String openid, String date, String s,
                                           Pageable pageable);

    @Query(value = "select * from (select d.id,d.sn,s.shopName,d.`description`,d.remark,ifnull(SUM(o.amount),0) as orderAmount,count(o.id) as orderCount, ifnull(SUM(m.amount),0) as commAmount,count(r.id) as count from device d LEFT JOIN shop s on s.id = d.shopId LEFT JOIN `order` o on o.deviceSN = d.sn and o.orderStatus = 2 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 LEFT JOIN money_log m on m.orderId = o.id and m.targetId = ?1 and m.targetType = 'agent' LEFT JOIN `order` r on r.deviceSN = d.sn  where s.agentId = ?1 and d.sn like ?3  GROUP BY d.id,s.id ) as d", nativeQuery = true)
    List<JSONObject> usageAnalysisByDeviceByMonth(String openid, String date, String s,
                                                  Pageable pageable);

    /*****************************************************************************************/

    @Query(value = "select a.id,a.shopName,a.deviceCount,b.orderCount,b.orderAmount,(b.orderCount / a.deviceCount / 30) as rate, round(b.orderCount / a.deviceCount / 30 * 100, 2) as formatRate,c.commAmount from (select s.id,s.shopName,count(d.id) as deviceCount from shop s LEFT JOIN device d on d.shopId = s.id where s.id = ?2 GROUP BY s.id) as a,(select s.id,s.shopName,count(o.orderNo) as orderCount,ifnull(sum(o.amount),0) as orderAmount from shop s LEFT JOIN `order` o on o.shopId = s.id and DATE_FORMAT(o.`end`, '%Y-%m') = ?3 and o.orderStatus = 2 where s.id = ?2 GROUP BY s.id) as b,(select ifnull(sum(m.amount),0) as commAmount from `order` o LEFT JOIN money_log m on m.orderId = o.id and DATE_FORMAT(o.`end`, '%Y-%m') = ?3 where o.shopId = ?2 and m.targetType = 'agent' and m.targetId = ?1) as c", nativeQuery = true)
    JSONObject usageAnalysisByShopInfoInfoByMonth(String openid, Integer id, String date);

    @Query(value = "SELECT DATE_FORMAT(o.`end`, '%Y-%m') AS time ,DATE_FORMAT(o.`end`, '%Y-%m') AS date , DATE_FORMAT(o.`end`, '%Y-%m') AS mon , COUNT(*) AS num FROM `order` o where o.shopId = ?1 and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 GROUP BY o.`end` ORDER BY o.`end`", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoOrderCountByMonth(Integer id, String date);

    @Query(value = "select d.sn, d.remark,count(o.id) as count from device d LEFT JOIN `order` o on o.deviceSN = d.sn and DATE_FORMAT(o.`end`, '%Y-%m') = ?2 where d.shopId = ?1 GROUP BY d.id ORDER BY count desc", nativeQuery = true)
    List<JSONObject>  usageAnalysisByShopInfoLeastByMonth(Integer id, String date);

    @Query(value = "select a.*,ifnull((select count(o.id) from `order` o where o.shopId = ?1 and date(o.`end`) = a.time and o.orderStatus = 2)/(select count(d.id) from device d where shopId = ?1), 0) as rate " +
                   "from (" +
                   "    SELECT ?2 as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 1 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 2 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 3 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 4 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 5 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 6 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 7 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 8 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 9 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 10 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 11 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 12 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 13 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 14 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 15 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 16 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 17 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 18 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 19 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 20 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 21 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 22 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 23 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 24 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 25 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 26 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 27 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 28 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 29 day) as time" +
                   "    union all" +
                   "    SELECT DATE_ADD(?2, interval 30 day) as time" +
                   ") as a ORDER BY a.time", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoWeeklyByMonth(Integer id, String date);

    @Query(value = "SELECT s.*,ifnull(round(s.count/count(d.id),1),0) as rate from (select s.id,s.shopName,count(o.id) as count from shop s LEFT JOIN `order` o on o.shopId = s.id where s.agentId = ?1 GROUP BY s.id) as s LEFT JOIN device d on d.shopId = s.id  GROUP BY s.id ORDER BY rate desc  LIMIT 7", nativeQuery = true)
    List<JSONObject> usageAnalysisByShopInfoRateByMonth(String openid);

    Page<Agent> findAllByLevel(Integer level, Pageable pageable);

    List<Agent> findAllByLevel(Integer level);

    Page<Agent> findAllByNameLikeAndLevel(String name,Integer level , Pageable pageable);

    Page<Agent> findAllById(Integer id, Pageable pageable);

    List<Agent> getAllByParent(String openid);

    @Query(value = "SELECT uid,`name`,sum(amount) as amount FROM `withdrawal` where identitytype = ?1 GROUP BY uid,`name` ORDER BY amount desc", nativeQuery = true)
    List<JSONObject> withdrawal(String type);

    @Query(value = "SELECT ifnull(sum(amount),0.00) as amount FROM `withdrawal` where identitytype = ?1 ", nativeQuery = true)
    Float withdrawalAmount(String agent);
}
